﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Project.Co_DataTransformationPrototype.Data
{
    
    public static class SQLUtilities
    {

        internal static bool testConnection(string connectionString)
        {
            bool blnSucces = false;

            if (!string.IsNullOrEmpty(connectionString.Trim()))
            {
                // Create & open a SqlConnection, and dispose of it afterwards
                try
                {
                    SqlConnection connection = new SqlConnection(connectionString);
                    connection.Open();
                    connection.Close();
                    blnSucces = true;
                }
                catch(SqlException)
                {
                    throw;
                }
            }

            return blnSucces;
        }

        internal static int executeNonQuerySQL(string connectionString, string sqlStatement)
        {
            try
            {
                int intResult = 0;
                intResult = executeNonQuery(connectionString, CommandType.Text, sqlStatement);
                return intResult;
            }
            catch
            {
                throw;
            }
        }

        internal static DataTable executeSQL(string connectionString, string sqlStatement)
        {
            DataTable dtResult = new DataTable();

            try
            {
                dtResult = executeDataset(connectionString, CommandType.Text, sqlStatement).Tables[0];
            }
            catch(SqlException)
            {
                dtResult = null;
            }

            return dtResult;
        }

        internal static void executeSqlFile(string resourceFilePath)
        {
            System.IO.FileStream strm;
            System.IO.StreamReader reader;
            try
            {

                string sql = "";
                string sqlFile = resourceFilePath;
                if (!string.IsNullOrEmpty(sqlFile))
                {
                    strm = System.IO.File.OpenRead(sqlFile);
                    reader = new System.IO.StreamReader(strm);
                    sql = reader.ReadToEnd();

                    System.Text.RegularExpressions.Regex regex = new System.Text.RegularExpressions.Regex("^GO", System.Text.RegularExpressions.RegexOptions.IgnoreCase | System.Text.RegularExpressions.RegexOptions.Multiline);
                    string[] lines = regex.Split(sql);

                    SqlConnection connection = new SqlConnection(DatabaseSettings.GetInstance().ConnectionString);
                    connection.Open();
                    SqlTransaction transaction = connection.BeginTransaction();
                    SqlCommand cmd = connection.CreateCommand();

                    cmd.Connection = connection;
                    cmd.Transaction = transaction;

                    //executes each sql command
                    foreach (string line in lines)
                    {
                        if (line.Length > 0)
                        {
                            cmd.CommandText = line;
                            cmd.CommandType = CommandType.Text;

                            try
                            {
                                cmd.ExecuteNonQuery();
                            }
                            catch (SqlException)
                            {
                                transaction.Rollback();
                                throw;
                            }
                        }
                    }
                    transaction.Commit();

                    if (connection.State != ConnectionState.Closed)
                        connection.Close();

                    connection = null;
                }
            }
            catch
            {
                throw;
            }
            finally
            {
                strm = null;
                reader = null;
            }
        }

        private static int executeNonQuery(string connectionString, CommandType commandType, string commandText)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");

            // Create & open a SqlConnection, and dispose of it after we are done
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                // Call the overload that takes a connection in place of the connection string
                return createAndExecuteNonQueryCommand(connection, commandType, commandText, (SqlParameter[])null);
            }
        }

        private static int createAndExecuteNonQueryCommand(SqlConnection connection, CommandType commandType, string commandText, SqlParameter[] commandParameters)
        {
            if (connection == null) throw new ArgumentNullException("connection");

            // Create a command and prepare it for execution
            SqlCommand cmd = new SqlCommand();
            bool mustCloseConnection = false;
            prepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);

            // Finally, execute the command
            int retval = cmd.ExecuteNonQuery();

            // Detach the SqlParameters from the command object, so they can be used again
            cmd.Parameters.Clear();
            if (mustCloseConnection)
                connection.Close();
            return retval;
        }

        private static DataSet executeDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");

            // Create & open a SqlConnection, and dispose of it after we are done
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                // Call the overload that takes a connection in place of the connection string
                return createAndExecuteCommand(connection, commandType, commandText, commandParameters);
            }
        }

        private static DataSet createAndExecuteCommand(SqlConnection connection, CommandType commandType, string commandText, SqlParameter[] commandParameters)
        {
            if (connection == null) throw new ArgumentNullException("connection");

            // Create a command and prepare it for execution
            SqlCommand cmd = new SqlCommand();
            bool mustCloseConnection = false;
            prepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);

            // Create the DataAdapter & DataSet
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                DataSet ds = new DataSet();

                // Fill the DataSet using default values for DataTable names, etc
                da.Fill(ds);

                // Detach the SqlParameters from the command object, so they can be used again
                cmd.Parameters.Clear();

                if (mustCloseConnection)
                    connection.Close();

                // Return the dataset
                return ds;
            }
        }

        private static void prepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, out bool mustCloseConnection)
        {
            if (command == null) throw new ArgumentNullException("command");
            if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");

            // If the provided connection is not open, we will open it
            if (connection.State != ConnectionState.Open)
            {
                mustCloseConnection = true;
                connection.Open();
            }
            else
            {
                mustCloseConnection = false;
            }

            // Associate the connection with the command
            command.Connection = connection;

            // Set the command text (stored procedure name or SQL statement)
            command.CommandText = commandText;

            // If we were provided a transaction, assign it
            if (transaction != null)
            {
                if (transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
                command.Transaction = transaction;
            }

            // Set the command type
            command.CommandType = commandType;

            // Attach the command parameters if they are provided
            if (commandParameters != null)
            {
                attachParameters(command, commandParameters);
            }
            return;
        }

        private static void attachParameters(SqlCommand command, SqlParameter[] commandParameters)
        {
            if (command == null) throw new ArgumentNullException("command");
            if (commandParameters != null)
            {
                foreach (SqlParameter p in commandParameters)
                {
                    if (p != null)
                    {
                        // Check for derived output value with no value assigned
                        if ((p.Direction == ParameterDirection.InputOutput ||
                            p.Direction == ParameterDirection.Input) &&
                            (p.Value == null))
                        {
                            p.Value = DBNull.Value;
                        }
                        command.Parameters.Add(p);
                    }
                }
            }
        }
   
    }
}
